package com.symc.sims0213xfw.dao.impl;

import com.symc.sims0213xfw.bean.Department;
import com.symc.sims0213xfw.dao.DepartmentDao;
import com.symc.sims0213xfw.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * @Author: 凤文  沈阳医学院2019级医学信息工程 0213
 * @CreateTime: 2022/01/26 13:33
 * @Description:
 */
public class DepartmentDaoImpl implements DepartmentDao {
    private static QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());

    @Override
    public List<Department> queryListVisibleDepartment() {
        String sql = "SELECT id,NAME FROM department WHERE isVisible = 1";
        List<Department> departments = null;
        try {
            departments = qr.query(sql,
                    new BeanListHandler<Department>(Department.class));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return departments;
    }

    @Override
    public Department queryVisibleDepartment(Integer cid) {
        Department department = null;
        String sql = "select * from department where cid =? and isVisible=1";
        try {
            department = qr.query(sql,new BeanHandler<>(Department.class),cid);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return department;
    }

    @Override
    public Department queryDepartment(Integer cid) {
        Department department = null;
        String sql = "select * from department where cid =? ";
        try {
            department = qr.query(sql,new BeanHandler<>(Department.class),cid);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return department;
    }

    @Override
    public List<Department> queryDepartmentByCollegeName(String collegeName) {
        String sql ="SELECT * FROM college c,department d WHERE c.name=? AND " +
                "c.id=d.cid ";
        List<Department> departments = null;
        try {
            departments = qr.query(sql, new BeanListHandler<Department>(Department.class),
                    collegeName);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return departments;
    }

    @Override
    public int addDepartment(Integer id, String name, Integer cid) {
        String sql = "INSERT INTO department VALUES(?,?,?,1)";
        int i = 0;
        try {
            i = qr.update(sql, new Object[]{id, name, cid});
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return i;
    }

    @Override
    public int updateDepartment(Integer id, String name, Integer cid) {
        String sql = "UPDATE department SET NAME=?,cid=?,isVisible=1 WHERE " +
                "id=?";
        int i = 0;
        try {
            i =qr.update(sql,new Object[]{name,cid,id});
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return i;
    }

    @Override
    public int deleteVisibleDepartment(Integer id) {
        String sql = "UPDATE department SET isVisible=0 WHERE id=?";
        int i = 0;
        try {
            i = qr.update(sql,id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return i;
    }
}
